---------------------------------------------------
-- @name: cardinalities
-- @author: dion cho
-- @description: various cardianality issues
--------------------------------------------------

@capture_on

select * from v$version;

-- create objects
drop table t1 purge;

create table t1(c1 int, c2 int, c3 date, c4 varchar2(14));

-- generate data and gather statistics
insert into t1
select 
  level, -- highly selective
  mod(abs(dbms_random.random),10), -- highly unselective
  to_date('20080301','yyyymmdd') - mod(level,100),
  to_char(to_date('20080301','yyyymmdd') - mod(level,100),'yyyymmdd')
from dual
connect by level <= 10000
;

-- gather stats "without" histogram
exec dbms_stats.gather_table_stats(user, 't1', -
  method_opt=>'for all columns size 1');

@tab_stat t1


-----------------------------------------------
-- estimation vs. real
explain plan for
select count(*)
from t1
where c1 between 1 and 10
;

@plan

select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 between 1 and 10
;

@stat

explain plan for
select count(*)
from t1
where c2 = 1
;

@plan

select /*+ gather_plan_statistics */
  count(*)
from t1
where c2 = 1
;

@stat

------------------------------------------------
-- simple and normal cases

-- case1 : highly selective + constants
explain plan for
select * 
from t1
where c1 = 1
;

@plan

-- case2 : highly unselective + constants
explain plan for
select * 
from t1
where c2 = 1
;

@plan

-- case3: highly selective + bind variable
explain plan for
select * 
from t1
where c1 = :b1
;

@plan


-- case3: highly unselective + bind variable
explain plan for
select * 
from t1
where c2 = :b1
;

@plan


-- case4: between 
explain plan for
select *
from t1
where c1 between 1 and 100
;

@plan

-- case5: and predicate
explain plan for
select *
from t1
where c1 between 1 and 100 and
      c2 = 1
;

@plan
    
-- case6: or predicate
explain plan for
select *
from t1
where c1 between 1 and 100 or
      c2 = 1
;

@plan
    
-- case7: date
explain plan for
select *
from t1
where c3 = to_date('20080201','yyyymmdd')
;

@plan

explain plan for
select *
from t1
where c3 between to_date('20071231','yyyymmdd')
      and to_date('20080110','yyyymmdd')
;

@plan


-- case8: when date is simulated using varchar
explain plan for
select *
from t1
where c4 = '20080201'
;

@plan

explain plan for
select *
from t1
where c4 between '20071231'
      and '20080110'
;

@plan

-----------------------------------------
-- exceptional cases

-- case1: bind variable and range
explain plan for
select *
from t1
where c1 > :b1
;

@plan

explain plan for
select *
from t1
where c1 >= :b1
;

@plan


explain plan for
select *
from t1
where c2 > :b1
;

@plan


-- between 
explain plan for
select *
from t1
where c1 between :b1 and :b2
;

@plan


-- case2: function predicate
create or replace function f1(v1 int)
  return int
is
begin
  return v1;
end f1;
/

-- 1% magic number
explain plan for
select *
from t1
where c1 = f1(c1)
;

@plan

explain plan for
select * 
from t1
where c2 = f1(1)
;

@plan

-- 1% magic number
explain plan for
select *
from t1
where f1(c1) = 1
;

@plan

explain plan for
select *
from t1
where f1(c2) = 1
;

@plan


explain plan for
select *
from t1
where f1(c2) = :b1
;

@plan

-- 5% magic number
explain plan for
select *
from t1
where f1(c1) > 1
;

@plan


explain plan for
select *
from t1
where f1(c1) between 1 and 100
;

@plan

explain plan for
select *
from t1
where f1(c1) between :b1 and :b2
;

@plan

-- case3: like predicate
explain plan for
select *
from t1
where c4 like '20080101'
;

@plan

-- another 5% rule
explain plan for
select *
from t1
where c4 like :b1
;

@plan

-- but with _like_with_bind_as_equality parameter
explain plan for
select /*+ opt_param('_like_with_bind_as_equality', 'true') */ 
  *
from t1
where c4 like :b1
;

@plan

explain plan for
select *
from t1
where c4 like '2008%'
;

@plan

-- another 5% rule
explain plan for
select *
from t1
where c4 like '%2008'
;

@plan

-- another 5% rule
explain plan for
select *
from t1
where c4 like '%2008%'
;

@plan


---------------------------------------------------
-- some mysterious cases
 
-- case1: skewed data
drop table t1 purge;
create table t1(c1 varchar2(1), c2 int);

create index t1_n1 on t1(c1);

insert into t1
select 'A', level
from dual
connect by level <= 10000
union all
select 'B', level
from dual
connect by level <= 10
;

commit;

exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for all columns size 1', -
      no_invalidate=>false);

select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 = 'A'
;

@stat

select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 = 'B'
;

@stat

exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for all columns size skewonly', -
      no_invalidate=>false);

select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 = 'A'
;

@stat

select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 = 'B'
;

@stat


-- case2: correlated columns
-- create correlated column
drop table t1 purge;
create table t1(c1 varchar2(1), c2 int);

insert into t1
select 'A', 1
from dual connect by level <= 1000
union all
select 'B', 2
from dual connect by level <= 100
union all
select 'C', 2
from dual connect by level <= 100
;

commit;

exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for all columns size skewonly', -
      no_invalidate=>false);


-- this is correct
select /*+ gather_plan_statistics */
  count(*)
from t1
where 
    c1 = 'A'
;

@stat


-- this is *in*correct (under-populating)
select /*+ gather_plan_statistics */
  count(*)
from t1
where 
    c1 = 'A' and
    c2 = 1
;

@stat

-- this is *in*correct (over-populating)
select /*+ gather_plan_statistics */
  count(*)
from t1
where 
    c1 = 'A' and
    c2 = 2
;

@stat


-- 11g solves the problem
begin
  dbms_stats.gather_table_stats(user,'t1', 
      method_opt=>'for columns  (c1, c2) size skewonly',
      no_invalidate=>false);
end;
/

-- fantastic!!
select /*+ gather_plan_statistics */
  count(*)
from t1
where 
    c1 = 'A' and
    c2 = 1
;

@stat

-- great!!
select /*+ gather_plan_statistics */
  count(*)
from t1
where 
    c1 = 'A' and
    c2 = 2
;

@stat


-- but, how about this case?
drop table t1 purge;
create table t1(c1 int, c2 int);

insert into t1
select 
  ceil(level/1000), 
  level
from dual
connect by level <= 10000
;

commit;

@gather t1

exec dbms_stats.gather_table_stats(user,'t1', -
      method_opt=>'for columns  (c1, c2) size skewonly', -
      no_invalidate=>false);


-- this is *in*correct (under-populating)
select /*+ gather_plan_statistics */
  count(*)
from t1
where 
    c1 = 1 and
    c2 between 1 and 1000
;

@stat

-- this is *in*correct (over-populating)
select /*+ gather_plan_statistics */
  count(*)
from t1
where 
    c1 = 2 and
    c2 between 1 and 1000
;

@stat


-- case3: join
drop table t1 purge;
drop table t2 purge;

create table t1(
  c1 int, 
    c2 int,
    c3 int,
    c4 int
)
;

create table t2(
    c1 int, 
    c2 int,
    c3 int,
    c4 varchar2(1)
)
;

insert into t1
select
  1,
  level,
  mod(level, 100)+1,
  '1'
from dual
connect by level <= 1000
;

insert into t2
select
  2,
  level,
  case 
    when level <= 99 then level
    else 0
  end,
  '2'
from dual
connect by level <= 1000
;

@gather t1
@gather t2


select /*+ gather_statistics */
  count(*)
from t1, t2
where t1.c1 = t2.c1
;

@stat


select /*+ gather_statistics */
  count(*)
from t1, t2
where t1.c2 = t2.c2
;

@stat


select /*+ gather_statistics */
  count(*)
from t1, t2
where t1.c3 = t2.c3
;

@stat

-- with histogram
exec dbms_stats.gather_table_stats(user, 't1', -
      method_opt=>'for columns c3 size skewonly', -
      no_invalidate=>false);
      
 -- with histogram
exec dbms_stats.gather_table_stats(user, 't2', -
      method_opt=>'for columns c3 size skewonly', -
      no_invalidate=>false);
       

select /*+ gather_statistics */
  count(*)
from t1, t2
where t1.c3 = t2.c3
;

@stat



select /*+ gather_statistics */
  count(*)
from t1, t2
where t1.c4 = t2.c4
;

@stat



-- query transformation and join cardinality
-- semi join. seems bad?
select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 in (select c1 from t2)
;

@stat


-- semi join, but seems good?
select /*+ gather_plan_statistics */
  count(*)
from t1
where c3 in (select c3 from t2)
;

@stat


-- what if the unnesting does not work? seems bad?
select /* gather_plan_statistics */
  count(*)
from 
  (
  select /*+ no_merge */ t1.c1, t1.c2
  from t1
  where c3 in (select /*+ no_unnest */ c3 from t2)
  ) v, t2
where
  v.c1 = t2.c1
;

@stat



-- not in. negate of "in"?
select /*+ gather_plan_statistics */
  count(*)
from t1
where c1 not in (select c1 from t2 where c1 is not null)
      and c1 is not null
;

@stat

select /*+ gather_plan_statistics */
  count(*)
from t1
where c3 not in (select c3 from t2 where c3 is not null)
      and c3 is not null
;

@stat

@capture_off
